package com.aree.caipiao.shsq.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao;
import com.aree.caipiao.shsq.model.ShuangSeQiuResult;

/**
 * 双色球持久层的jdbc实现
 * @author hp-pc
 *
 */
@Repository("ShuangSeQiuResultJdbcDao")
public class ShuangSeQiuResultDao extends GenericDao<ShuangSeQiuResult> implements IShuangSeQiuResultDao {
	private static final String TABLE_NAME = "t_shuangseqiu";

	public ShuangSeQiuResultDao() {
		super();
	}

	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao#getById(java.lang.Long)
	 */
	public ShuangSeQiuResult getById(Long id) {
		String sql = "select * from " + TABLE_NAME + " where id = ?";
		try {
			return super.query(sql, id);
		} catch (SQLException e) {
			log.error("查询id=[" + id + "]的记录出错", e);
		}
		return null;
	}

	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao#getBySerial(java.lang.String)
	 */
	public ShuangSeQiuResult getBySerial(String serial) {
		String sql = "select * from " + TABLE_NAME + " where serial = ?";
		try {
			return super.query(sql, serial);
		} catch (SQLException e) {
			log.error("查询id=[" + serial + "]的记录出错", e);
		}
		return null;
	}
	
	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao#getHezhiTongji()
	 */
	public List<Integer> getHezhiTongji() {
		String querySql = "select red1+red2+red3+red4+red5+red6+blue from " + TABLE_NAME;
		Connection connection = openConn();
	    PreparedStatement ps;
		try {
			ps = connection.prepareStatement(querySql);
			ResultSet rs = ps.executeQuery();
		    List<Integer> result = new ArrayList<Integer>();
		    if (rs == null || !rs.next()) return result;
		    do {
		    	result.add(rs.getInt(1));
		    } while (rs.next());
		    return result;
		} catch (SQLException e) {
			log.error("和值统计出错", e);
		}
		return null;
	}

	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao#saveShuangSeQiuResult(com.aree.caipiao.shsq.model.ShuangSeQiuResult)
	 */
	public void saveShuangSeQiuResult(ShuangSeQiuResult result) {
		String insertSql = "insert into " + TABLE_NAME + "(date,serial,sale,top1Num,top2Num,blue,red1,red2,red3,red4,red5,red6) value(?,?,?,?,?,?,?,?,?,?,?,?)";
		try {
			super.execute(insertSql, result.getDate(), result.getSerial(), result.getSale(), result.getTop1Num(), 
					result.getTop2Num(), result.getBlue(), result.getRed1(), result.getRed2(), result.getRed3(), 
					result.getRed4(), result.getRed5(), result.getRed6());
			log.error("保存[" + result + "成功]");
		} catch (SQLException e) {
			log.error("保存[" + result + "失败]", e);
		}
	}

	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao#updateShuangSeQiuResult(com.aree.caipiao.shsq.model.ShuangSeQiuResult)
	 */
	public void updateShuangSeQiuResult(ShuangSeQiuResult result) {
		String insertSql = "update " + TABLE_NAME + " set date=?,serial=?,sale=?,top1Num=?,top2Num=?,blue=?,red1=?,red2=?,red3=?,red4=?,red5=?,red6=?";
		try {
			super.execute(insertSql, result.getDate(), result.getSerial(), result.getSale(), result.getTop1Num(), 
					result.getTop2Num(), result.getBlue(), result.getRed1(), result.getRed2(), result.getRed3(), 
					result.getRed4(), result.getRed5(), result.getRed6());
			log.error("更新[" + result + "成功]");
		} catch (SQLException e) {
			log.error("更新[" + result + "失败]", e);
		}
	}

	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.jdbc.GenericDao#buildFromResultSet(java.sql.ResultSet)
	 */
	@Override
	protected ShuangSeQiuResult buildFromResultSet(ResultSet rs) throws SQLException {
		long id = rs.getLong("id");
		String date = rs.getString("date");
		String serial = rs.getString("serial");
		String sale = rs.getString("sale");
		String top1Num = rs.getString("top1Num");
		String top2Num = rs.getString("top2Num");
		int blue = rs.getInt("blue");
		int red1 = rs.getInt("red1");
		int red2 = rs.getInt("red2");
		int red3 = rs.getInt("red3");
		int red4 = rs.getInt("red4");
		int red5 = rs.getInt("red5");
		int red6 = rs.getInt("red6");
		ShuangSeQiuResult ssq = new ShuangSeQiuResult();
		ssq.setBlue(blue);
		ssq.setDate(date);
		ssq.setId(id);
		ssq.setRed1(red1);
		ssq.setRed2(red2);
		ssq.setRed3(red3);
		ssq.setRed4(red4);
		ssq.setRed5(red5);
		ssq.setRed6(red6);
		ssq.setSerial(serial);
		if (StringUtils.isNotEmpty(sale)) ssq.setSale(Integer.parseInt(sale));
		if (StringUtils.isNotEmpty(top1Num)) ssq.setTop1Num(Integer.parseInt(top1Num));
		if (StringUtils.isNotEmpty(top2Num)) ssq.setTop1Num(Integer.parseInt(top2Num));
		return ssq;
	}

	/* (non-Javadoc)
	 * @see com.aree.caipiao.shsq.dao.IShuangSeQiuResultDao#count()
	 */
	public int count() {
		String querySql = "select count(*) from " + TABLE_NAME;
		Connection connection = openConn();
	    PreparedStatement ps;
		try {
			ps = connection.prepareStatement(querySql);
			ResultSet rs = ps.executeQuery();
		    if (rs == null || !rs.next()) return 0;
		    return rs.getInt(1);
		} catch (SQLException e) {
			log.error("和值统计出错", e);
		}
		return 0;
	}
	
	public static void main(String[] args) {
		ShuangSeQiuResultDao shuangSeQiuResultDao = new ShuangSeQiuResultDao();
		System.out.println(shuangSeQiuResultDao.count());
	}
}
